using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using Microsoft.ApplicationBlocks.Data;
using Utilities;

namespace Core
{
    /// <summary>
    /// The object help to query to database. 
    /// </summary>
    public class DataAccessLayer
    {
        #region Helper

        /// <summary>
        /// Get stored procedure name
        /// </summary>
        /// <param name="type">The type of object.</param>
        /// <param name="action">The action to database.</param>
        /// <returns></returns>
        private static string GetStoreProcedureName(Type type, DbAction action)
        {
            try
            {
                return ((TableAttribute)(type.GetCustomAttributes(typeof(TableAttribute), true)[0])).TableName + "_" + action.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Create a new instance of object with specified type.
        /// </summary>
        /// <param name="type">The type of object.</param>
        /// <returns>The new instance of object with default constructor.</returns>
        public static object NewInstance(Type type)
        {
            try
            {
                ConstructorInfo ci = type.GetConstructor(new Type[0]);
                if (ci == null)
                {
                    return null;
                }
                return ci.Invoke(null);
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// Get the properties collection contain in obj
        /// </summary>
        /// <param name="obj">Object to get properties.</param>
        /// <returns>Return a list contain properties collection.</returns>
        private static List<PropertyInfo> GetProperties(Type type)
        {
            List<PropertyInfo> properties = new List<PropertyInfo>();
            foreach (PropertyInfo property in type.GetProperties())
            {
                if (property.GetCustomAttributes(true) == null || property.GetCustomAttributes(true).Length == 0)
                    continue;

                properties.Add(property);
            }

            return properties;
        }

        /// <summary>
        /// Get list parameters
        /// </summary>
        /// <param name="objCondition">Obj to get value.</param>
        /// <param name="properties">List of properties.</param>
        /// <returns>The parameters array.</returns>
        private static SqlParameter[] GetParameters(object objCondition, List<PropertyInfo> properties)
        {
            PropertyInfo lastExceptionProperty = null;

            SqlParameter[] sqlParam = new SqlParameter[properties.Count];
            int count = 0;
            foreach (PropertyInfo property in properties)
            {
                if (property.Name == "LastException")
                    lastExceptionProperty = property;

                if (property.GetCustomAttributes(true) == null || property.GetCustomAttributes(true).Length == 0)
                    continue;

                ColumnAttribute attribute = ((ColumnAttribute)(property.GetCustomAttributes(true)[0]));

                sqlParam[count] = new SqlParameter("@" + attribute.ColumnName, attribute.ColumnType, attribute.ColumnLength);
                sqlParam[count].Value = property.GetValue(objCondition, null);
                
                if (property.Name == "SortType")
                {
                    SortType SorTypeValue = Protector.EnumTypeByName<SortType>(property.GetValue(objCondition, null), SortType.ASC);
                    if (SorTypeValue == SortType.ASC)
                        sqlParam[count].Value = true;
                    else
                        sqlParam[count].Value = false;
                }

                count++;
            }

            return sqlParam;
        }

        /// <summary>
        /// Check exist of a field in a DataReader
        /// </summary>
        /// <param name="reader">The DataReader</param>
        /// <param name="fieldName">The field name</param>
        /// <returns>
        /// true: exist
        /// false: not exist
        /// </returns>
        private static bool FieldIsExists(IDataReader reader, string fieldName)
        {
            reader.GetSchemaTable().DefaultView.RowFilter = string.Format("ColumnName= '{0}'", fieldName);
            return (reader.GetSchemaTable().DefaultView.Count > 0);
        }

        /// <summary>
        /// Get list result from database
        /// </summary>
        /// <param name="type">Type of object.</param>
        /// <param name="sqlDrd">The SqlDataReader contain records set.</param>
        /// <param name="properties">List of properties obj object.</param>
        /// <returns>Return the list object contain results set.</returns>
        private static List<object> GetListResult(Type type, SqlDataReader sqlDrd, List<PropertyInfo> properties)
        {
            try
            {
                List<object> objCollection = new List<object>();
                while (sqlDrd.Read())
                {
                    object itemObject = NewInstance(type);
                    foreach (PropertyInfo property in properties)
                    {
                        if (property.GetCustomAttributes(true) == null || property.GetCustomAttributes(true).Length == 0)
                            continue;

                        ColumnAttribute attribute = ((ColumnAttribute)(property.GetCustomAttributes(true)[0]));

                        if (!FieldIsExists(sqlDrd, attribute.ColumnName))
                            continue;

                        if (sqlDrd[attribute.ColumnName] != DBNull.Value)
                        {
                            property.SetValue(itemObject, sqlDrd[attribute.ColumnName], null);
                        }
                    }
                    objCollection.Add(itemObject);
                }
                return objCollection;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Methods

        /// <summary>
        /// Insert an obj mapping to database.
        /// </summary>
        /// <param name="obj">The obj will be insert to database.</param>
        /// <returns></returns>
        public static int Insert(object obj, ref Exception outException)
        {
            string strStoreName;
            int intID = 0;
            int iCount = 0, iFieldLength = 0;
            int primaryKeyIndex = -1;

            strStoreName = GetStoreProcedureName(obj.GetType(), DbAction.Insert);

            foreach (PropertyInfo property in (obj.GetType()).GetProperties())
            {
                if (property.GetCustomAttributes(true) == null || property.GetCustomAttributes(true).Length == 0)
                    continue;

                if (property.Name == "SortBy" || property.Name == "SortType")
                    continue;

                iFieldLength++;
            }

            SqlParameter[] sqlParam = new SqlParameter[iFieldLength];
            PropertyInfo primaryKeyPropertyInfo = null;

            foreach (PropertyInfo property in (obj.GetType()).GetProperties())
            {
                if (property.GetCustomAttributes(true) == null || property.GetCustomAttributes(true).Length == 0)
                    continue;

                if (property.Name == "SortBy" || property.Name == "SortType")
                    continue;

                ColumnAttribute attribute = ((ColumnAttribute)(property.GetCustomAttributes(true)[0]));
                sqlParam[iCount] = new SqlParameter("@" + attribute.ColumnName, attribute.ColumnType);

                if (attribute.IsIdentity == true)
                {
                    primaryKeyIndex = iCount;
                    primaryKeyPropertyInfo = property;
                    sqlParam[iCount].Direction = ParameterDirection.Output;
                }
                else
                {
                    sqlParam[iCount].Value = property.GetValue(obj, null);
                }

                iCount++;

            }

            try
            {
                SqlHelper.ExecuteNonQuery(ConnectionString.strConn, CommandType.StoredProcedure, strStoreName, sqlParam);
            }
            catch (Exception ex)
            {
                outException = ex;
            }

            if (primaryKeyIndex != -1 && sqlParam[primaryKeyIndex].Value != null)
            {
                intID = Protector.Int(sqlParam[primaryKeyIndex].Value.ToString(), 0);
                primaryKeyPropertyInfo.SetValue(obj, intID, null);
            }

            return intID;
        }

        /// <summary>
        /// Update an obj mapping to database.
        /// </summary>
        /// <param name="obj">The obj will be updated.</param>
        /// <returns></returns>
        public static bool Update(object obj, ref Exception outException)
        {
            string strStoreName;
            int iCount = 0;
            int iFieldLength = 0;
            bool isSuccess = false;
            int iEffect = -1;

            strStoreName = GetStoreProcedureName(obj.GetType(), DbAction.Update);

            foreach (PropertyInfo property in (obj.GetType()).GetProperties())
            {
                if (property.GetCustomAttributes(true) == null || property.GetCustomAttributes(true).Length == 0)
                    continue;

                if (property.Name == "SortBy" || property.Name == "SortType")
                    continue;

                iFieldLength++;
            }

            SqlParameter[] sqlParam = new SqlParameter[iFieldLength];

            foreach (PropertyInfo property in (obj.GetType()).GetProperties())
            {
                if (property.GetCustomAttributes(true) == null || property.GetCustomAttributes(true).Length == 0)
                    continue;

                if (property.Name == "SortBy" || property.Name == "SortType")
                    continue;

                ColumnAttribute attribute = ((ColumnAttribute)(property.GetCustomAttributes(true)[0]));
                sqlParam[iCount] = new SqlParameter("@" + attribute.ColumnName, attribute.ColumnType);
                sqlParam[iCount].Value = property.GetValue(obj, null);
                iCount++;
            }

            try
            {
                iEffect = SqlHelper.ExecuteNonQuery(ConnectionString.strConn, CommandType.StoredProcedure, strStoreName, sqlParam);
            }
            catch (Exception ex)
            {
                outException = ex;
                return false;
            }

            if (iEffect >= 0)
            {
                isSuccess = true;
            }
            return isSuccess;
        }

        /// <summary>
        /// Get the first obj match objcondition
        /// </summary>
        /// <param name="objCondition">The obj contain conditions to filter</param>
        /// <returns></returns>
        public static object GetObject(object objCondition, ref Exception outException)
        {
            List<object> objCollection = GetListObject(objCondition, ref outException);
            if (objCollection == null || objCollection.Count == 0)
                return null;
            return objCollection[0];
        }

        /// <summary>
        /// Get all objects mapping to database
        /// </summary>
        /// <param name="objCondition">The obj contain conditions to filter</param>
        /// <returns>Return the list object match conditions.</returns>
        public static List<object> GetListObject(object objCondition, ref Exception outException)
        {
            if (objCondition == null)
                return null;

            Type type = objCondition.GetType();
            string strStoreName = GetStoreProcedureName(type, DbAction.Select);

            List<PropertyInfo> properties = GetProperties(type);

            SqlParameter[] sqlParam = GetParameters(objCondition, properties);

            SqlDataReader sqlDrd = null;
            try
            {
                sqlDrd = SqlHelper.ExecuteReader(ConnectionString.strConn, CommandType.StoredProcedure, strStoreName, sqlParam);
            }
            catch (Exception ex)
            {
                outException = ex;
                return null;
            }
            List<object> objCollection = GetListResult(type, sqlDrd, properties);

            sqlDrd.Close();
            sqlDrd.Dispose();

            return objCollection;
        }

        /// <summary>
        /// Get all objects mapping to database with pagging
        /// </summary>
        /// <param name="objCondition">The obj contain conditions to filter</param>
        /// <param name="page">The page need get objects</param>
        /// <param name="pageSize">The size of page</param>
        /// <returns>Return the list object contain in page match conditions.</returns>
        public static List<object> GetListObject(object objCondition, int page, int pageSize, ref int total, ref Exception outException)
        {
            if (objCondition == null)
                return null;

            Type type = objCondition.GetType();
            string strStoreName = GetStoreProcedureName(type, DbAction.Select);

            List<PropertyInfo> properties = GetProperties(type);

            SqlParameter[] sqlParam = new SqlParameter[properties.Count + 3];
            int count = 0;
             
            PropertyInfo lastExceptionProperty = null;

            foreach (PropertyInfo property in properties)
            {
                if (property.Name == "LastException")
                    lastExceptionProperty = property;

                if (property.GetCustomAttributes(true) == null || property.GetCustomAttributes(true).Length == 0)
                    continue;

                ColumnAttribute attribute = ((ColumnAttribute)(property.GetCustomAttributes(true)[0]));

                sqlParam[count] = new SqlParameter("@" + attribute.ColumnName, attribute.ColumnType, attribute.ColumnLength);
                sqlParam[count].Value = property.GetValue(objCondition, null);

                if (property.Name == "SortType")
                {
                    SortType SorTypeValue = Protector.EnumTypeByName<SortType>(property.GetValue(objCondition, null), SortType.ASC);
                    if (SorTypeValue == SortType.ASC)
                        sqlParam[count].Value = true;
                    else
                        sqlParam[count].Value = false;
                }
                count++;
            }
            sqlParam[count] = new SqlParameter("@page", page);
            count++;
            sqlParam[count] = new SqlParameter("@pageSize", pageSize);
            count++;

            sqlParam[count] = new SqlParameter("@returnValue", 0);
            sqlParam[count].Direction = ParameterDirection.ReturnValue;

            SqlDataReader sqlDrd = null;
            try
            {
                sqlDrd = SqlHelper.ExecuteReader(ConnectionString.strConn, CommandType.StoredProcedure, strStoreName, sqlParam);
            }
            catch (Exception ex)
            {
                outException = ex;
                return null;
            }

            List<object> objCollection = GetListResult(type, sqlDrd, properties);

            sqlDrd.Close();
            sqlDrd.Dispose();

            total = Protector.Int(sqlParam[sqlParam.Length - 1].Value);

            return objCollection;
        }

        /// <summary>
        /// Find all obj match objcondition
        /// </summary>
        /// <param name="objCondition">The obj contain conditions to filter</param>
        /// <returns>Return the list object match conditions.</returns>
        public static List<object> FindListObjects(object objCondition, ref Exception outException)
        {
            List<PropertyInfo> properties = GetProperties(objCondition.GetType());
            foreach (PropertyInfo property in properties)
            {
                if (property.PropertyType != typeof(System.String)) continue;

                object value = property.GetValue(objCondition, null);
                if (value == null) continue;

                if (property.Name != "SortBy")
                    property.SetValue(objCondition, string.Format("%{0}%", value), null);
                else
                    property.SetValue(objCondition, value, null);
            }
            return GetListObject(objCondition, ref outException);
        }

        /// <summary>
        /// Find all obj match objcondition
        /// </summary>
        /// <param name="objCondition">The obj contain conditions to filter</param>
        /// <param name="page">The page need get objects</param>
        /// <param name="pageSize">The size of page</param>
        /// <returns>Return the list object match conditions.</returns>
        public static List<object> FindListObjects(object objCondition, int page, int pageSize, ref int total, ref Exception outException)
        {
            List<PropertyInfo> properties = GetProperties(objCondition.GetType());
            foreach (PropertyInfo property in properties)
            {
                if (property.PropertyType != typeof(System.String)) continue;

                object value = property.GetValue(objCondition, null);
                if (value == null) continue;

                if (property.Name != "SortBy")
                    property.SetValue(objCondition, string.Format("%{0}%", value), null);
                else
                    property.SetValue(objCondition, value, null);
            }
            return GetListObject(objCondition, page, pageSize, ref total, ref outException);
        }

        /// <summary>
        /// Delete an obj mapping to database
        /// </summary>
        /// <param name="obj">The obj will be deleted.</param>
        /// <returns></returns>
        public static bool Delete(object obj, ref Exception outException)
        {
            string strStoreName;
            bool isSuccess = false;
            int iCount = 0;
            int totalKeys = 0;
            int iEffect = -1;

            strStoreName = GetStoreProcedureName(obj.GetType(), DbAction.Delete);

            foreach (PropertyInfo property in (obj.GetType()).GetProperties())
            {
                if (property.GetCustomAttributes(true) == null || property.GetCustomAttributes(true).Length == 0)
                    continue;

                if (property.Name == "SortBy" || property.Name == "SortType")
                    continue;

                ColumnAttribute attribute = ((ColumnAttribute)(property.GetCustomAttributes(true)[0]));
                if (attribute.IsPrimaryKey == true)
                {
                    totalKeys++;
                }
            }

            SqlParameter[] sqlParam = new SqlParameter[totalKeys];
            foreach (PropertyInfo property in (obj.GetType()).GetProperties())
            {
                if (property.GetCustomAttributes(true) == null || property.GetCustomAttributes(true).Length == 0)
                    continue;

                if (property.Name == "SortBy" || property.Name == "SortType")
                    continue;

                ColumnAttribute attribute = ((ColumnAttribute)(property.GetCustomAttributes(true)[0]));
                if (attribute.IsPrimaryKey == true)
                {
                    sqlParam[iCount] = new SqlParameter("@" + attribute.ColumnName, attribute.ColumnType, attribute.ColumnLength);
                    sqlParam[iCount].Value = property.GetValue(obj, null);
                    iCount++;
                }
            }

            try
            {
                iEffect = SqlHelper.ExecuteNonQuery(ConnectionString.strConn, CommandType.StoredProcedure, strStoreName, sqlParam);
            }
            catch (Exception ex)
            {
                outException = ex;
                return false;
            }
            if (iEffect >= 0)
            {
                isSuccess = true;

            }
            return isSuccess;
        }

        /// <summary>
        /// Execute a sqlcommand
        /// </summary>
        /// <param name="commandText">The sql command, maybe SqlStatement or Stored procedure name.</param>
        /// <param name="commandType">The type of sql sql command: text or storedprocedure</param>
        /// <param name="sqlParam">The sql params for sql command</param>
        /// <param name="outException">The out exception if has during execute the sqlcommand.</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string commandText, CommandType commandType, SqlParameter[] sqlParam, ref Exception outException)
        {
            DataSet ds = null;
            try
            {
                ds = SqlHelper.ExecuteDataset(ConnectionString.strConn, commandType, commandText, sqlParam);
            }
            catch (Exception ex)
            {
                outException = ex;
                return null;
            }

            if (ds == null || ds.Tables[0] == null)
                return null;

            return ds.Tables[0];
        }

        public static SqlDataReader ExecuteDataReader(string commandText, CommandType commandType, SqlParameter[] sqlParam, ref int total, ref Exception outException)
        {
            int totalParam = sqlParam.Length;
            SqlParameter[] sqlParamProcess = new SqlParameter[totalParam+1];
            for (int i = 0; i < totalParam; i++)
            {
                sqlParamProcess[i] = sqlParam[i];
            }

            sqlParamProcess[totalParam] = new SqlParameter("@returnValue", 0);
            sqlParamProcess[totalParam].Direction = ParameterDirection.ReturnValue;

            SqlDataReader sqlDrd = null;
            try
            {
                sqlDrd = SqlHelper.ExecuteReader(ConnectionString.strConn, commandType, commandText, sqlParam);
            }
            catch (Exception ex)
            {
                outException = ex;
                return null;
            }

            //sqlDrd.Close();
            //sqlDrd.Dispose();

            total = Protector.Int(sqlParam[sqlParam.Length - 1].Value);

            return sqlDrd;
        }
        #endregion
    }
}
